const connection = require("../app/database");

class CommunityService {
  async getCategoryBannersService() {
    const statement =
      "select id banner_id ,banner_cover,banner_title FROM category_banners";
    try {
      const [result] = await connection.execute(statement);
      return result;
    } catch (error) {
      console.log(error);
    }

    // connection
    //   .execute(statement)
    //   .then((res) => {
    //     console.log(res);
    //   })
    //   .catch((err) => {
    //     console.log(err);
    //   });
  }

  async getCategoryThemeService() {
    const statement = "select * FROM category_theme";
    try {
      const [result] = await connection.execute(statement);
      return result;
    } catch (error) {
      console.log(error);
    }

    // connection
    //   .execute(statement)
    //   .then((res) => {
    //     console.log(res);
    //   })
    //   .catch((err) => {
    //     console.log(err);
    //   });
  }

  async getThemeIncludeCategoryService() {
    const statement = `
    SELECT 
    ct.id AS themeId, 
    ct.theme_name AS themeName, 
    (JSON_ARRAYAGG(JSON_OBJECT(
				'id',c.id, 'label',c.category_label ,'name',c.category_name, 'avatar',c.category_avatar,
				'article_count',c.article_count
		)))  AS category 
    FROM 
        category_theme AS ct 
    LEFT JOIN 
        category AS c 
    ON 
        ct.id = c.theme_id 
    GROUP BY 
        ct.id;
    `;
    try {
      const [result] = await connection.execute(statement);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  async getCategoryByThemeIdService(themeId) {
    const statement = "SELECT * FROM category WHERE category.theme_id = ?";
    try {
      const [result] = await connection.execute(statement, [themeId]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  async getCategoryByRandomService(randomCount) {
    const statement = "select * FROM category ORDER BY RAND() LIMIT ?";
    try {
      const [result] = await connection.execute(statement, [randomCount]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  async getActivityClassifyService() {
    const statement = "select * FROM activity_classify";
    try {
      const [result] = await connection.execute(statement);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  async getActivityByRecommendService() {
    const statement = "select * FROM activity a where a.recommened = 1";
    try {
      const [result] = await connection.execute(statement);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  async releaseActivityService(activityParameter) {
    const {
      uid,
      activity_title,
      activity_describe,
      activity_classify,
      activity_place,
      activity_cover,
      people_ceiling,
      start_time,
      end_time,
    } = activityParameter;
    // console.log("server", activityParameter);
    const request_time = new Date();
    // console.log("request_time", request_time);
    const statement = `
    INSERT INTO activity
    (uid, activity_title, activity_describe, activity_classify, activity_place, activity_cover, people_ceiling, request_time, start_time, end_time, update_time)
    VALUES
    (?,?,?,?,?,?,?,?,?,?,?) `;
    try {
      const [result] = await connection.execute(statement, [
        uid,
        activity_title,
        activity_describe,
        activity_classify,
        activity_place,
        activity_cover,
        people_ceiling,
        request_time,
        start_time,
        end_time,
        request_time,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  async getActivityService(limit, offset) {
    const activities = {
      activityLists: [],
      activityTotal: 0,
    };

    const statement1 =
      " SELECT COUNT(*) as total FROM activity WHERE audit_state = 2 ";
    try {
      const [activityTotalResult] = await connection.execute(statement1);
      // console.log("animalTotalResult", animalTotalResult[0].total);
      activities.activityTotal = activityTotalResult[0].total;
    } catch (error) {
      console.log(error);
    }

    const statement2 = `SELECT a.id id,a.activity_cover,a.activity_describe,a.activity_place,a.activity_title,a.audit_time,a.end_time,a.people_ceiling,a.recommened,a.request_time,a.start_time,a.update_time,
    JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account) AS user,
    JSON_OBJECT('id',ac.id,'name',ac.name) AS activity_classify
    FROM activity a 
    LEFT JOIN user u ON u.id = a.uid 
    LEFT JOIN activity_classify ac ON ac.id = a.activity_classify
    WHERE audit_state = 2 
    LIMIT ? OFFSET ?;`;
    try {
      const [activityListsResult] = await connection.execute(statement2, [
        limit,
        offset,
      ]);
      activities.activityLists = activityListsResult;
    } catch (error) {
      console.log(error);
    }

    return activities;
  }

  async complaintServer(complaintParameter) {
    // console.log(complaintParameter);
    const { uid, activityid, articleid, body } = complaintParameter;
    console.log(uid, body, activityid, articleid);
    try {
      if (activityid) {
        const statement =
          "INSERT INTO `complaint` (uid, body,activityid) VALUES (?,?,?);";
        const [result] = await connection.execute(statement, [
          uid,
          body,
          activityid,
        ]);
        return result;
      }
      if (articleid) {
        const statement =
          "INSERT INTO `complaint` (uid, body,articleid) VALUES (?,?,?);";
        const [result] = await connection.execute(statement, [
          uid,
          body,
          articleid,
        ]);
        return result;
      }
    } catch (error) {
      console.log(error);
    }
  }

  async opinionServer(uid, body) {
    try {
      const statement = "INSERT INTO `opinion` (uid, body) VALUES (?,?);";
      const [result] = await connection.execute(statement, [uid, body]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }
}

module.exports = new CommunityService();
